 ALTER FUNCTION fn_standard_cost_info_report( 
 	@FG_PART_NUMBER VARCHAR(50), 
 	@LOTS_DATE_OUT_FROM VARCHAR(20), 
 	@LOTS_DATE_OUT_TO VARCHAR(20), 
 	@STEP_SIZE INT ) 
 RETURNS
	@FN_RESULTS TABLE( FG_PART_NUMBER VARCHAR(50), GROSS_DIE_WAFER INT, DATE_START DATETIME, DATE_END DATETIME,
 				FAB_VENDOR VARCHAR(50), FAB_CYCLE_TIME INT, FAB_YIELD NUMERIC( 9, 2), FAB_COST NUMERIC( 9, 2),
 				SORT_VENDOR VARCHAR(50), SORT_CYCLE_TIME INT, SORT_YIELD NUMERIC( 9, 2), SORT_COST NUMERIC( 9, 2),
 				ASSEMBLY_VENDOR VARCHAR(50), ASSEMBLY_CYCLE_TIME INT, ASSEMBLY_YIELD NUMERIC( 9, 2), ASSEMBLY_COST NUMERIC( 9, 2),
 				TEST_VENDOR VARCHAR(50), TEST_CYCLE_TIME INT, TEST_YIELD NUMERIC( 9, 2), TEST_COST NUMERIC( 9, 2),
 				FG_VENDOR VARCHAR(50) )

 BEGIN
 	DECLARE @fg_process_step_id INT
 	DECLARE @grossDiePerWafer INT
 	DECLARE @date_out_start DATETIME
 	DECLARE @date_out_end DATETIME
 	DECLARE @date_out_start_iterator DATETIME
 	declare @quarter varchar(50)
 	declare @year varchar(4)
 
 	DECLARE @processStepId INT
 	DECLARE @processType VARCHAR(50)
 	DECLARE @baseProcessType VARCHAR(50)
 
 	DECLARE @fab_vendorName VARCHAR(50)
 	DECLARE @fab_cycleTime INT
 	DECLARE @fab_yield FLOAT
 	DECLARE @fab_cost FLOAT
 	DECLARE @total_fab_cycleTime INT
 	DECLARE @total_fab_yield FLOAT
 	DECLARE @total_fab_cost FLOAT
 	DECLARE @fab_location_id INT
 
 	DECLARE @sort_vendorName VARCHAR(50)
 	DECLARE @sort_cycleTime INT
 	DECLARE @sort_yield FLOAT
 	DECLARE @sort_cost FLOAT
 	DECLARE @total_sort_cycleTime INT
 	DECLARE @total_sort_yield FLOAT
 	DECLARE @total_sort_cost FLOAT
 	DECLARE @sort_location_id INT
 
 	DECLARE @assembly_vendorName VARCHAR(50)
 	DECLARE @assembly_cycleTime INT
 	DECLARE @assembly_yield FLOAT
 	DECLARE @assembly_cost FLOAT
 	DECLARE @total_assembly_cycleTime INT
 	DECLARE @total_assembly_yield FLOAT
 	DECLARE @total_assembly_cost FLOAT
 	DECLARE @assembly_location_id INT
 
 	DECLARE @test_vendorName VARCHAR(50)
 	DECLARE @test_cycleTime INT
 	DECLARE @test_yield FLOAT
 	DECLARE @test_cost FLOAT
 	DECLARE @total_test_cycleTime INT
 	DECLARE @total_test_yield FLOAT
 	DECLARE @total_test_cost FLOAT
 	DECLARE @test_location_id INT
 
 	DECLARE @fg_vendorName VARCHAR(50)
 
 	DECLARE @RESULTS TABLE( FG_PART_NUMBER VARCHAR(50), GROSS_DIE_WAFER INT, DATE_START DATETIME, DATE_END DATETIME,
 				FAB_VENDOR VARCHAR(50), FAB_CYCLE_TIME INT, FAB_YIELD NUMERIC( 9, 2), FAB_COST NUMERIC( 9, 2),
 				SORT_VENDOR VARCHAR(50), SORT_CYCLE_TIME INT, SORT_YIELD NUMERIC( 9, 2), SORT_COST NUMERIC( 9, 2),
 				ASSEMBLY_VENDOR VARCHAR(50), ASSEMBLY_CYCLE_TIME INT, ASSEMBLY_YIELD NUMERIC( 9, 2), ASSEMBLY_COST NUMERIC( 9, 2),
 				TEST_VENDOR VARCHAR(50), TEST_CYCLE_TIME INT, TEST_YIELD NUMERIC( 9, 2), TEST_COST NUMERIC( 9, 2),
 				FG_VENDOR VARCHAR(50) )
 
 	DECLARE @process_step_cursor CURSOR
 
 	IF @LOTS_DATE_OUT_FROM IS NULL OR @LOTS_DATE_OUT_FROM = '' OR @LOTS_DATE_OUT_FROM = '0'
 	BEGIN
 		SELECT @LOTS_DATE_OUT_FROM = '01/01/1900'
 	END
 
 	IF @LOTS_DATE_OUT_TO IS NULL OR @LOTS_DATE_OUT_TO = '' OR @LOTS_DATE_OUT_TO = '0'
 	BEGIN
 		SELECT @LOTS_DATE_OUT_TO = '01/01/9999'
 	END
 	
 	SELECT @date_out_start = CC_Start_Date FROM COMPANY_Calendar WHERE CC_YEAR = ( SELECT MIN( DATEPART ( yyyy , Fin_Year ) ) FROM UPDATE_VIEW_COST_TABLE  )
	
	IF @date_out_start < CAST( @LOTS_DATE_OUT_FROM AS datetime)
	BEGIN
		SET @date_out_start =CAST( @LOTS_DATE_OUT_FROM AS datetime)
	END
	
 	SELECT @date_out_end = CC_End_Date FROM COMPANY_Calendar WHERE CC_YEAR = ( SELECT MAX( DATEPART ( yyyy , Fin_Year ) ) FROM UPDATE_VIEW_COST_TABLE  )

	IF @date_out_end > CAST( @LOTS_DATE_OUT_TO AS datetime)
	BEGIN
		SET @date_out_end =  CAST( @LOTS_DATE_OUT_TO AS datetime)
	END	
 
 	SELECT @fg_process_step_id = PROCESSSTEP_ID FROM PRODUCT WHERE PART_NUMBER = @FG_PART_NUMBER AND P_MARKETING_PART_NUMBER IS NOT NULL
 	SELECT TOP 1 @fg_vendorName = L_Vendor_Name FROM PROCESS_STEP_LOCATION_TABLE, LOCATION WHERE PROCESS_STEP_LOCATION_TABLE.LOCATION_ID = LOCATION.LOCATION_ID AND PROCESSSTEP_ID = @fg_process_step_id ORDER BY PSLT_PRIORITY
 	
 	SELECT @date_out_start_iterator = @date_out_start
 
 	WHILE @date_out_start_iterator <= @date_out_end
 	BEGIN
 		SET  @fab_vendorName = null
 		SET  @fab_cycleTime = 0
 		SET  @fab_yield = 1
 		SET  @fab_cost = 0.0
 		SET  @total_fab_cycleTime = 0
 		SET  @total_fab_yield = 1
 		SET  @total_fab_cost = 0.0
 		SET  @fab_location_id = 0
 		
 		SET  @sort_vendorName = null
 		SET  @sort_cycleTime = 0
 		SET  @sort_yield = 1
 		SET  @sort_cost = 0.0
 		SET  @total_sort_cycleTime = 0
 		SET  @total_sort_yield = 1
 		SET  @total_sort_cost = 0.0
 		SET  @sort_location_id = 0
 		
 		SET  @assembly_vendorName = null
 		SET  @assembly_cycleTime = 0
 		SET  @assembly_yield = 1
 		SET  @assembly_cost = 0.0
 		SET  @total_assembly_cycleTime = 0
 		SET  @total_assembly_yield = 1
 		SET  @total_assembly_cost = 0.00
 		SET  @assembly_location_id = 0
 		
 		SET  @test_vendorName = null
 		SET  @test_cycleTime = 0
 		SET  @test_yield = 1
 		SET  @test_cost = 0.0
 		SET  @total_test_cycleTime = 1 
 		SET  @total_test_yield = 1
 		SET  @total_test_cost = 0.0
 		SET  @test_location_id = 0
 
 		SET @process_step_cursor = CURSOR FOR SELECT ProcessStep_ID, PS_Type, Process_Type_Name FROM FN_GET_PROCESS_FLOW_BACKWARD_BY_ID( @fg_process_step_id ) WHERE Process_Type_Name IN ( 'FAB', 'SORT', 'Assembly', 'TEST' ) 
 		select TOP 1 @quarter = LTRIM( RTRIM (CQC_Quarter )), @year = CC_Year from Company_calendar, Company_Quarterly_calendar where Company_calendar.CC_YearID = Company_Quarterly_calendar.CQC_YearID and CQC_START_Date <= @date_out_start_iterator order by CQC_START_Date DESC
 		
 		OPEN @process_step_cursor
 		FETCH NEXT FROM @process_step_cursor INTO @processStepId, @processType, @baseProcessType
 		WHILE @@FETCH_STATUS = 0
 		BEGIN
 			IF @baseProcessType = 'FAB'
 			BEGIN
 				SELECT @grossDiePerWafer = Parameter_Value FROM Process_Parameter WHERE processStep_ID = @processStepId and Parameter_Key = 'Gross Die/Wafer'
 				SELECT TOP 1 @fab_cycleTime = PSLT_Cycle_Time_Normal, @fab_vendorName = L_Vendor_Name, @fab_location_id = PROCESS_STEP_LOCATION_TABLE.Location_ID FROM PROCESS_STEP_LOCATION_TABLE, LOCATION WHERE PROCESS_STEP_LOCATION_TABLE.LOCATION_ID = LOCATION.LOCATION_ID AND PROCESSSTEP_ID = @processStepId ORDER BY PSLT_PRIORITY
 				
 				IF @quarter = 'QUARTER 1' or @quarter = 'Quarter1'
 				BEGIN
 					select @fab_cost = Q1_Standard_Cost, @fab_yield = Q1_yield from Update_View_Cost_Table where ProcessStep_ID = @processStepId and Location_ID = @fab_location_id and DATEPART ( yyyy , Fin_Year ) = @year
 				END
 				ELSE IF @quarter = 'QUARTER 2' or @quarter = 'Quarter2'
 				BEGIN
 					select @fab_cost = Q2_Standard_Cost, @fab_yield = Q2_yield from Update_View_Cost_Table where ProcessStep_ID = @processStepId and Location_ID = @fab_location_id and DATEPART ( yyyy , Fin_Year ) = @year
 				END
 				ELSE IF @quarter = 'QUARTER 3' or @quarter = 'Quarter3'
 				BEGIN
 					select @fab_cost = Q3_Standard_Cost, @fab_yield = Q3_yield  from Update_View_Cost_Table where ProcessStep_ID = @processStepId and Location_ID = @fab_location_id and DATEPART ( yyyy , Fin_Year ) = @year
 				END
 				ELSE IF @quarter = 'QUARTER 4' or @quarter = 'Quarter4'
 				BEGIN
 					select @fab_cost = Q4_Standard_Cost, @fab_yield = Q4_yield  from Update_View_Cost_Table where ProcessStep_ID = @processStepId and Location_ID = @fab_location_id and DATEPART ( yyyy , Fin_Year ) = @year
 				END
 				
 				IF @fab_cycleTime IS NOT NULL
 				BEGIN
 					SET @total_fab_cycleTime = @total_fab_cycleTime + @fab_cycleTime
 				END
 
 				IF @fab_cost IS NOT NULL
 				BEGIN
 					SET @total_fab_cost = @total_fab_cost + @fab_cost
 				END
 
 				IF @fab_yield IS NOT NULL
 				BEGIN
 					SET @total_fab_yield = ( @total_fab_yield * @fab_yield ) / 100
 				END
 			END
 			ELSE IF @baseProcessType = 'SORT'
 			BEGIN	
 				SELECT TOP 1 @sort_cycleTime = PSLT_Cycle_Time_Normal, @sort_vendorName = L_Vendor_Name, @sort_location_id = PROCESS_STEP_LOCATION_TABLE.Location_ID FROM PROCESS_STEP_LOCATION_TABLE, LOCATION WHERE PROCESS_STEP_LOCATION_TABLE.LOCATION_ID = LOCATION.LOCATION_ID AND PROCESSSTEP_ID = @processStepId ORDER BY PSLT_PRIORITY
 			
 				IF @quarter = 'Quarter 1' or @quarter = 'Quarter1'
 				BEGIN	
 					select @sort_cost = Q1_Standard_Cost, @sort_yield = Q1_yield from Update_View_Cost_Table where ProcessStep_ID = @processStepId and Location_ID = @sort_location_id and DATEPART ( yyyy , Fin_Year ) = @year
 				END
 				ELSE IF @quarter = 'Quarter 2' or @quarter = 'Quarter2'
 				BEGIN
 					select @sort_cost = Q2_Standard_Cost, @sort_yield = Q2_yield from Update_View_Cost_Table where ProcessStep_ID = @processStepId and Location_ID = @sort_location_id and DATEPART ( yyyy , Fin_Year ) = @year
 				END
 				ELSE IF @quarter = 'Quarter 3' or @quarter = 'Quarter3'
 				BEGIN
 					select @sort_cost = Q3_Standard_Cost, @sort_yield = Q3_yield  from Update_View_Cost_Table where ProcessStep_ID = @processStepId and Location_ID = @sort_location_id and DATEPART ( yyyy , Fin_Year ) = @year
 				END
 				ELSE IF  @quarter = 'Quarter 4' or @quarter = 'Quarter4'
 				BEGIN
 					select @sort_cost = Q4_Standard_Cost, @sort_yield = Q4_yield  from Update_View_Cost_Table where ProcessStep_ID = @processStepId and Location_ID = @sort_location_id and DATEPART ( yyyy , Fin_Year ) = @year
 				END
 
 				IF @sort_cycleTime IS NOT NULL
 				BEGIN
 					SET @total_sort_cycleTime = @total_sort_cycleTime + @sort_cycleTime
 				END
 
 				IF @sort_cost IS NOT NULL
 				BEGIN
 					SET @total_sort_cost = @total_sort_cost + @sort_cost
 				END
 
 				IF @sort_yield IS NOT NULL
 				BEGIN
 					SET @total_sort_yield = ( @total_sort_yield * @sort_yield ) / 100
 				END
 			END
 			ELSE IF @baseProcessType = 'Assembly'
 			BEGIN
 				SELECT TOP 1 @assembly_cycleTime = PSLT_Cycle_Time_Normal, @assembly_vendorName = L_Vendor_Name, @assembly_location_id = PROCESS_STEP_LOCATION_TABLE.Location_ID  FROM PROCESS_STEP_LOCATION_TABLE, LOCATION WHERE PROCESS_STEP_LOCATION_TABLE.LOCATION_ID = LOCATION.LOCATION_ID AND PROCESSSTEP_ID = @processStepId ORDER BY PSLT_PRIORITY
 				IF @quarter = 'Quarter 1' or @quarter = 'Quarter1'
 				BEGIN	
 					select @assembly_cost = Q1_Standard_Cost, @assembly_yield = Q1_yield from Update_View_Cost_Table where ProcessStep_ID = @processStepId and Location_ID = @assembly_location_id and DATEPART ( yyyy , Fin_Year ) = @year
 				END
 				ELSE IF @quarter = 'Quarter 2' or @quarter = 'Quarter2'
 				BEGIN
 					select @assembly_cost = Q2_Standard_Cost, @assembly_yield = Q2_yield from Update_View_Cost_Table where ProcessStep_ID = @processStepId and Location_ID = @assembly_location_id and DATEPART ( yyyy , Fin_Year ) = @year
 				END
 				ELSE IF @quarter = 'Quarter 3' or @quarter = 'Quarter3'
 				BEGIN
 					select @assembly_cost = Q3_Standard_Cost, @assembly_yield = Q3_yield  from Update_View_Cost_Table where ProcessStep_ID = @processStepId and Location_ID = @assembly_location_id and DATEPART ( yyyy , Fin_Year ) = @year
 				END
 				ELSE IF  @quarter = 'Quarter 4' or @quarter = 'Quarter4'
 				BEGIN
 					select @assembly_cost = Q4_Standard_Cost, @assembly_yield = Q4_yield  from Update_View_Cost_Table where ProcessStep_ID = @processStepId and Location_ID = @assembly_location_id and DATEPART ( yyyy , Fin_Year ) = @year
 				END
 
 				IF @assembly_cycleTime IS NOT NULL
 				BEGIN
 					SET @total_assembly_cycleTime = @total_assembly_cycleTime + @assembly_cycleTime
 				END
 
 				IF @assembly_cost IS NOT NULL
 				BEGIN
 					SET @total_assembly_cost = @total_assembly_cost + @assembly_cost
 				END
 
 				IF @assembly_yield IS NOT NULL
 				BEGIN
 					SET @total_assembly_yield = ( @total_assembly_yield * @assembly_yield ) / 100
 				END
 			END
 			ELSE IF @baseProcessType = 'TEST'
 			BEGIN
 				SELECT TOP 1 @test_cycleTime = PSLT_Cycle_Time_Normal, @test_vendorName = L_Vendor_Name, @test_location_id = PROCESS_STEP_LOCATION_TABLE.Location_ID  FROM PROCESS_STEP_LOCATION_TABLE, LOCATION WHERE PROCESS_STEP_LOCATION_TABLE.LOCATION_ID = LOCATION.LOCATION_ID AND PROCESSSTEP_ID = @processStepId ORDER BY PSLT_PRIORITY
 				IF @quarter = 'Quarter 1' or @quarter = 'Quarter1'
 				BEGIN	
 					select @test_cost = Q1_Standard_Cost, @test_yield = Q1_yield from Update_View_Cost_Table where ProcessStep_ID = @processStepId and Location_ID = @test_location_id and DATEPART ( yyyy , Fin_Year ) = @year
 				END
 				ELSE IF @quarter = 'Quarter 2' or @quarter = 'Quarter2'
 				BEGIN
 					select @test_cost = Q2_Standard_Cost, @test_yield = Q2_yield from Update_View_Cost_Table where ProcessStep_ID = @processStepId and Location_ID = @test_location_id and DATEPART ( yyyy , Fin_Year ) = @year
 				END
 				ELSE IF @quarter = 'Quarter 3' or @quarter = 'Quarter3'
 				BEGIN
 					select @test_cost = Q3_Standard_Cost, @test_yield = Q3_yield  from Update_View_Cost_Table where ProcessStep_ID = @processStepId and Location_ID = @test_location_id and DATEPART ( yyyy , Fin_Year ) = @year
 				END
 				ELSE IF  @quarter = 'Quarter 4' or @quarter = 'Quarter4'
 				BEGIN
 					select @test_cost = Q4_Standard_Cost, @test_yield = Q4_yield  from Update_View_Cost_Table where ProcessStep_ID = @processStepId and Location_ID = @test_location_id and DATEPART ( yyyy , Fin_Year ) = @year
 				END
 
 				IF @test_cycleTime IS NOT NULL
 				BEGIN
 					SET @total_test_cycleTime = @total_test_cycleTime + @test_cycleTime
 				END
 
 				IF @test_cost IS NOT NULL
 				BEGIN
 					SET @total_test_cost = @total_test_cost + @test_cost
 				END
 
 				IF @test_yield IS NOT NULL
 				BEGIN
 					SET @total_test_yield = (@total_test_yield * @test_yield)/100
 				END
 
 			END
 		
 			FETCH NEXT FROM @process_step_cursor INTO @processStepId, @processType, @baseProcessType
 		END
 		CLOSE @process_step_cursor
 	    	DEALLOCATE @process_step_cursor
 
 		INSERT INTO @RESULTS ( FG_PART_NUMBER, GROSS_DIE_WAFER, DATE_START, DATE_END,
 				FAB_VENDOR, FAB_CYCLE_TIME, FAB_YIELD, FAB_COST,
 				SORT_VENDOR, SORT_CYCLE_TIME, SORT_YIELD, SORT_COST,
 				ASSEMBLY_VENDOR, ASSEMBLY_CYCLE_TIME, ASSEMBLY_YIELD, ASSEMBLY_COST,
 				TEST_VENDOR, TEST_CYCLE_TIME, TEST_YIELD, TEST_COST, FG_VENDOR ) VALUES
 				( @FG_PART_NUMBER, @grossDiePerWafer, @date_out_start_iterator, ( @date_out_start_iterator + @STEP_SIZE ),
 				  @fab_vendorName, @total_fab_cycleTime, @total_fab_yield * 100, @total_fab_cost,
 				  @sort_vendorName, @total_sort_cycleTime, @total_sort_yield * 100, @total_sort_cost,
 				  @assembly_vendorName, @total_assembly_cycleTime, @total_assembly_yield * 100, @total_assembly_cost,
 				  @test_vendorName, @total_test_cycleTime, @total_test_yield*100, @total_test_cost, @fg_vendorName
 				 )
 
 
 		SELECT @date_out_start_iterator = @date_out_start_iterator + @STEP_SIZE
 	END	
 	INSERT @FN_RESULTS SELECT * FROM @results --WHERE DATE_START >= @LOTS_DATE_OUT_FROM AND DATE_END <= @LOTS_DATE_OUT_TO
	RETURN
 END